--USE [INFOED_REPORT]
--GO

IF OBJECT_ID('[dbo].[csp_CreateMetricSnapshot]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[csp_CreateMetricSnapshot]
    IF OBJECT_ID('[dbo].[csp_CreateMetricSnapshot]') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE [dbo].[csp_CreateMetricSnapshot] >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE [dbo].[csp_CreateMetricSnapshot] >>>'
END
go


/****** Object:  StoredProcedure [dbo].[csp_CreateMetricSnapshot]    Script Date: 12/22/2009 12:03:02 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE PROCEDURE [dbo].[csp_CreateMetricSnapshot]
	@PeriodType varchar(50)

AS

--Replace previous date range (i.e. '04/13/2008' and '04/20/2008') with the current week. Use the Sunday of the report week and Sunday of the following week.
--For monthly reports then the date range would be the 1st of the report month and the 1st of the next week (i.e. '04/01/2008' and '05/01/2008')
--For quarterly reports, they are broken up as follows: Q1= Sept-Dec, Q2 = Jan- Mar, Q3 =  April-Jun, Q4 = July-Sept so the date range for Q1 would be '09/01/2007' and '01/01/2008'
--MODIFIED - DG789 - 11/28/2009 to use UpdatedDate instead of CreatedDate to eliminate duplicates.
--DECLARE
--	@PeriodType varchar(50)

--SELECT
--	@PeriodType = 'Quarter'

DECLARE 
	@PeriodFrom datetime
,	@PeriodTo datetime
,	@PeriodDescription varchar(50)

IF @PeriodType = 'Week'
BEGIN
	SELECT
		@PeriodFrom = CONVERT(datetime,FLOOR(CONVERT(float,DATEADD(day,((DATEPART(dw,GETDATE())-1)+7)*-1,GETDATE()))))
	SELECT 
		@PeriodTo = DATEADD(day,7,@PeriodFrom)
	SELECT
		@PeriodDescription = 'Week Of ' + CONVERT(varchar(50),@PeriodFrom,101)
END

IF @PeriodType = 'Month'
BEGIN
	SELECT
		@PeriodFrom = CONVERT(datetime,FLOOR(CONVERT(float,DATEADD(month,-1,DATEADD(day,(DATEPART(day,GETDATE())-1)*-1,GETDATE())))))
	SELECT 
		@PeriodTo = DATEADD(month,1,@PeriodFrom)
	SELECT
		@PeriodDescription = DATENAME(m,@PeriodFrom) + ' ' + CONVERT(varchar(50),YEAR(@PeriodFrom))
END

IF @PeriodType = 'Quarter'
BEGIN

--	SELECT
--		@PeriodFrom = CONVERT(datetime,CONVERT(varchar(50),CalendarYear) + '-' + CONVERT(varchar(50),PeriodFromMonth) + '-01')
--	,	@PeriodDescription = 'Q' + CONVERT(varchar(50),FiscalQuarter) + ' ' + CONVERT(varchar(50),YEAR(FinishedMonth))
--	FROM
--	(
--		SELECT
--			PeriodFromMonth = CASE
--				WHEN FiscalQuarter = 1 THEN 10
--				WHEN FiscalQuarter = 2 THEN 1
--				WHEN FiscalQuarter = 3 THEN 4
--				WHEN FiscalQuarter = 4 THEN 7
--			END
--		,	CalendarYear = CASE
--				WHEN FiscalQuarter = 1 THEN YEAR(FinishedMonth)-1
--				WHEN FiscalQuarter = 2 THEN YEAR(FinishedMonth)
--				WHEN FiscalQuarter = 3 THEN YEAR(FinishedMonth)
--				WHEN FiscalQuarter = 4 THEN YEAR(FinishedMonth)
--			END
--		,	FiscalQuarter
--		,	FinishedMonth
--		FROM
--		(
--			SELECT
--				FiscalQuarter = CASE
--					WHEN FinishedMonthNumber = 12 THEN 1
--					WHEN FinishedMonthNumber < 12 AND FinishedMonthNumber >= 9 THEN 4
--					WHEN FinishedMonthNumber < 9 AND FinishedMonthNumber >= 6 THEN 3
--					WHEN FinishedMonthNumber < 6 AND FinishedMonthNumber >= 3 THEN 2
--					WHEN FinishedMonthNumber < 3 AND FinishedMonthNumber >= 0 THEN 1
--				END
--			,	CalendarQuarter = FinishedMonthNumber/3 + 1
--			,	FinishedMonth
--			FROM
--			(
--				SELECT
--					FinishedMonthNumber = MONTH(FinishedMonth)
--				,	FinishedMonth
--				FROM
--				(
--					SELECT
--						FinishedMonth = CONVERT(datetime,FLOOR(CONVERT(float,DATEADD(month,-1,DATEADD(day,(DATEPART(day,GETDATE())-1)*-1,GETDATE())))))
--				) AS x
--			) AS x
--		) AS x
--	) AS x
--	SELECT 
--		@PeriodTo = DATEADD(month,3,@PeriodFrom)


	SELECT
		@PeriodFrom = CONVERT(datetime,CONVERT(varchar(50),CalendarYear) + '-' + CONVERT(varchar(50),PeriodFromMonth) + '-01')
	,	@PeriodDescription = 'Q' + CONVERT(varchar(50),FiscalQuarter) + ' ' + CONVERT(varchar(50),YEAR(FinishedMonth))
	FROM
	(
		SELECT
			PeriodFromMonth = CASE
--				WHEN FiscalQuarter = 1 THEN 10
--				WHEN FiscalQuarter = 2 THEN 1
--				WHEN FiscalQuarter = 3 THEN 4
--				WHEN FiscalQuarter = 4 THEN 7

				WHEN FiscalQuarter = 1 THEN 1
				WHEN FiscalQuarter = 2 THEN 4
				WHEN FiscalQuarter = 3 THEN 7
				WHEN FiscalQuarter = 4 THEN 10
			END
		,	CalendarYear = CASE
--				WHEN FiscalQuarter = 1 THEN YEAR(FinishedMonth)-1
--				WHEN FiscalQuarter = 2 THEN YEAR(FinishedMonth)
--				WHEN FiscalQuarter = 3 THEN YEAR(FinishedMonth)
--				WHEN FiscalQuarter = 4 THEN YEAR(FinishedMonth)
	
				WHEN FiscalQuarter = 1 THEN YEAR(FinishedMonth)
				WHEN FiscalQuarter = 2 THEN YEAR(FinishedMonth)
				WHEN FiscalQuarter = 3 THEN YEAR(FinishedMonth)
				WHEN FiscalQuarter = 4 THEN YEAR(FinishedMonth)
			END
		,	FiscalQuarter
		,	FinishedMonth
		FROM
		(
			SELECT
				FiscalQuarter = CASE
					--WHEN FinishedMonthNumber = 12 THEN 1
					WHEN FinishedMonthNumber <= 12 AND FinishedMonthNumber > 9 THEN 4
					WHEN FinishedMonthNumber <= 9 AND FinishedMonthNumber > 6 THEN 3
					WHEN FinishedMonthNumber <= 6 AND FinishedMonthNumber > 3 THEN 2
					WHEN FinishedMonthNumber <= 3 AND FinishedMonthNumber > 0 THEN 1
				END
			,	CalendarQuarter = FinishedMonthNumber/3 + 1
			,	FinishedMonth
			FROM
			(
				SELECT
					FinishedMonthNumber = MONTH(FinishedMonth) 
				,	FinishedMonth
				FROM
				(
					SELECT
						FinishedMonth = CONVERT(datetime,FLOOR(CONVERT(float,DATEADD(month,-1,DATEADD(day,(DATEPART(day, GETDATE())-1)*-1,GETDATE())))))
				) AS x
			) AS x
		) AS x
	) AS x
	SELECT 
		@PeriodTo = DATEADD(month,3,@PeriodFrom)


END

--select @PeriodFrom,@PeriodTo,@PeriodDescription


-- Snapshot Table
DELETE FROM [InfoEdStaging].[dbo].[rptMetric_Snapshots] WHERE PeriodType=@PeriodType AND PeriodStartDate=@PeriodFrom

INSERT INTO [InfoEdStaging].[dbo].[rptMetric_Snapshots]
(
	PeriodType
,	PeriodStartDate
,	PeriodEndDate
,	PeriodDescription
,	SnapshotDate
)
VALUES
(
	@PeriodType
,	@PeriodFrom
,	@PeriodTo
,	@PeriodDescription
,	GETDATE()
)

--Pending Submissions --***Added pending budget
DELETE FROM [InfoEdStaging].[dbo].[rptMetric_PendingSubmissions] WHERE PeriodType=@PeriodType AND PeriodStartDate=@PeriodFrom

INSERT INTO [InfoEdStaging].[dbo].[rptMetric_PendingSubmissions]
           (PeriodType,PeriodStartDate,[Institution]
           ,[ProposalNumber]
           ,[PIName]
           ,[ChiefCode]
           ,[Sponsor]
           ,[ProposalStatus]
           ,[stat_date]
           ,[LastUpdatedBy]
           ,[DateReceived]
           ,[ProjectStartDate]
           ,[ProjectEndDate]
           ,[DeadlineDate]
           ,[PreAwardManager]
           ,[SubmissionDate]
           ,[BudgetAmount]
           ,[InstrumentType])

select distinct PeriodType=@PeriodType,PeriodStartDate=@PeriodFrom,case i.int_unit_code when '1200' then 'MGH' when '2200' then 'BWH' when '1700' then 'IHP' when '1400' then 'SRH' 
 when '1600' then 'MCL' when '0100' then 'PHS' when '2810' then 'FH' when '4100' then 'NWH' else isNull(i.int_unit_code,'') end as Institution, p.inst_no as ProposalNumber, 
 f.lname + ', ' + f.fname as PIName, u.int_unit_code as ChiefCode, sp.spon_name as Sponsor, 
 c.code_desc as ProposalStatus, s.stat_date, fs.lname + ', ' + fs.fname as LastUpdatedBy, up.p_date_24 as DateReceived, 
 p.pp_st_dt as ProjectStartDate, p.pp_end_dt as ProjectEndDate, 
 p.deadline as DeadlineDate, upf.lname + ', ' + upf.fname as PreAwardManager, submt_dt as SubmissionDate, 
 isNull(b.n_totyr,0) as BudgetAmount, up.p_sin_17 as InstrumentType
--into rptMetric_PendingSubmissions
from proposal p
inner join codetab c on c.arr_name = 'prop_stat' and p.prop_stat = c.codeID and c.code_desc like 'pending%'
inner join codetab pt on pt.codeid = p.prop_type and pt.arr_name = 'prop_type' and pt.code_desc = 'Master'
inner join prop_stat s on p.prop_no = s.prop_no and p.prop_stat = s.prop_stat
and s.id = (select max(id) from prop_stat where prop_no = s.prop_no and inst_code = s.inst_code and prop_stat = s.prop_stat)
left outer join faculty fs on s.unique_id = fs.unique_id
left outer join proppds o on o.prop_no = p.prop_no and o.inst_code = p.inst_code and o.first_pd = 1
left outer join faculty f on o.unique_id = f.unique_id
left outer join unit u on u.unit_code = p.unit_code and u.inst_code = 'partners'
left outer join unit i on len(i.deptID) = 4 and i.deptId = left(u.deptId,4) and i.int_unit_code is not null
left outer join sponspas sp on sp.spon_code = p.spon_code and sp.inst_code = p.inst_code
left outer join prop_u up on up.prop_no = p.prop_no and up.inst_code = p.inst_code
left outer join faculty upf on up.p_chr_1 = upf.unique_id
left outer join pd_grc_bgt b on p.prop_no = b.prop_no and p.inst_code = b.inst_code and b.rec_type = '1' and b.n_bgtyr = 1
where s.stat_date between @PeriodFrom and @PeriodTo -- Date Range 
and len(p.inst_no) = 11 and p.proj_title not like 'New Proposal For%'
order by up.p_date_24 desc

/**Start Of Award Activations **/
DELETE FROM [InfoEdStaging].[dbo].rptMetric_AwardActivations WHERE PeriodType=@PeriodType AND PeriodStartDate=@PeriodFrom

CREATE TABLE #tmpMetric_AwardActivations  (

	[Institution] [varchar] (32),
	[ProposalNumber] [varchar] (30),
	[PIName] [varchar] (250) NULL,
	[ChiefCode] [varchar] (32) NULL,
	[Sponsor] [varchar] (150) NULL,
	[SponosrID] [varchar] (100) NULL ,
	[ProposalStatus] [varchar] (80) NULL ,
	[AttachmentType] [varchar] (80) NULL ,
	[UploadDate] [datetime] NULL ,
	[UploadedBy] [varchar] (250) NULL ,
	[StatusChanged] [datetime] NULL ,
	[LastUpdatedBy] [varchar] (250) NULL ,
	[PSAccountNumber] [varchar] (50) NULL ,
	[PSFundCreationDate] [datetime] NULL ,
	[DaysAttachUploadToAccountCreation] [int] NULL, 
	[PostAwardManager] [varchar] (250) NULL ,
	[BudgetAmount] [money] NULL, 
	[InstrumentType] [varchar] (60) NULL 
) 

--Award Activations --Modified version of NOGA_FEAA (added columns)
insert into #tmpMetric_AwardActivations
select distinct case i.int_unit_code when '1200' then 'MGH' when '2200' then 'BWH' when '1700' then 'IHP' when '1400' then 'SRH' 
 when '1600' then 'MCL' when '0100' then 'PHS' when '2810' then 'FH' when '4100' then 'NWH' else isNull(i.int_unit_code,'') end as Institution, m.projnumber as ProposalNumber, f.lname + ', ' + f.fname as PIName, 
 u.int_unit_code as ChiefCode, sp.spon_name as Sponsor, isNull(isNull(s.spon_awd,s.spon_id),'') as SponsorID, st.code_desc as ProposalStatus,
 null as AttachmentType, null as UploadDate, null as UploadedBy,
 ps.stat_date as StatusChanged, pst.lname + ', ' + pst.fname as LastUpdatedBy, '#' + pma.instaccountno as AccountNo, pma.createdate as DateAccountLinked, 
 null as DaysAttachUploadToAccountCreation,
 fpa.lname + ', ' + fpa.fname as PostAwardManager, null as BudgetAmount, pa.p_sin_17 as InstrumentType 
from Projmain m
inner join proposal s on s.prop_no = m.prop_no and s.inst_code = m.inst_code
inner join pt_acct a on a.prop_no = m.prop_no and a.inst_code = m.inst_code and len(a.hacct_no) = 6
inner join pmaccount pma on a.hacct_no = pma.instaccountno 
inner join codetab st on st.codeid = s.prop_stat and st.arr_name = 'prop_stat'
inner join codetab pt on pt.codeid = s.prop_type and pt.arr_name = 'prop_type' --and pt.code_desc = 'Master'
left outer join proppds o on o.prop_no = m.prop_no and o.inst_code = m.inst_code and o.first_pd = 1
left outer join faculty f on o.unique_id = f.unique_id
left outer join unit u on u.unit_code = s.unit_code and u.inst_code = 'partners'
left outer join unit i on len(i.deptID) = 4 and i.deptId = left(u.deptId,4) and i.int_unit_code is not null
left outer join sponspas sp on sp.spon_code = s.spon_code
inner join prop_stat ps on ps.prop_no = s.prop_no and ps.inst_code = s.inst_code and ps.prop_stat = s.prop_stat
	and ps.stat_date  = (select min(stat_date) from prop_stat where prop_no = s.prop_no and inst_code = s.inst_code and prop_stat = s.prop_stat)
left outer join faculty pst on ps.unique_id = pst.unique_id
left outer join prop_u pa on pa.prop_no = s.prop_no and pa.inst_code = s.inst_code
left outer join faculty fpa on pa.p_chr_2 = fpa.unique_id
where len(m.projnumber) = 11 and pma.createdate between @PeriodFrom and @PeriodTo -- Date Range
order by stat_date desc


update #tmpMetric_AwardActivations 
set BudgetAmount = isNull((select sum(isNull(b.n_totyr,0)) from pd_grc_bgt b
			inner join codetab c on b.bud_stat = c.codeid and c.arr_name = 'awd_stat' and c.code_desc = 'Awarded' and b.rec_type = '3'
			where b.hacct = replace(#tmpMetric_AwardActivations.PSAccountNumber,'#','')),0)


update #tmpMetric_AwardActivations 
set	AttachmentType = c.code_desc, 
 	UploadDate = v.UploadDate, 
	UploadedBy = pf.lname + ', ' + pf.fname
from #tmpMetric_AwardActivations  t
inner join Projmain m on t.ProposalNumber = m.ProjNumber and m.system = 'pt'
inner join proposal s on s.prop_no = m.prop_no and s.inst_code = m.inst_code 
left outer join pt_project ppt on m.prop_no = ppt.child and m.inst_code = ppt.child_inst_code and ppt.subcontract = 0
left outer join projmain ppm on ppt.prop_no = ppm.prop_no and ppt.inst_code = ppm.inst_code and ppm.system = 'pt'
inner join ProjDoc d on (m.projid = d.projid or d.projid = ppm.projid) 
inner join projdocver v on v.docid = d.docid and v.uploaddate is not null 
inner join codetab c on c.code = d.doctype and c.arr_name = 'UP_LOAD' and c.code_desc in ('Notice of Award','Award Notice', 'Subcontract Agreement In', 'Advance Fund Setup Form', 'Agreement')
			and (isNull(v.UploadDate,'') = (select min(isNull(vv.UploadDate,'')) from projdoc dd inner join projdocver vv on dd.docID = vv.docID 
					inner join codetab cc on cc.code = dd.doctype and cc.arr_name = 'UP_LOAD' and cc.code_desc in ('Notice of Award','Award Notice', 'Subcontract Agreement In', 'Advance Fund Setup Form', 'Agreement')
					where dd.projID = m.projID) or v.UploadDate is null) 
left outer join faculty pf on pf.unique_id = v.UploadOwner

--Create cursor to calculate the dates and eliminate weekends
DECLARE @AttachmentUploadDate as datetime, @PSFundCreationDate as datetime, @PSAccountNumber as varchar(50)
DECLARE @DaysAttachUploadToAccountCreation as int, @ProposalNumber as varchar(30)

DECLARE BusinessDays_Cursor CURSOR FOR 
SELECT UploadDate, PSFundCreationDate, ProposalNumber, PSAccountNumber FROM #tmpMetric_AwardActivations

OPEN BusinessDays_Cursor

FETCH NEXT FROM BusinessDays_Cursor 
INTO @AttachmentUploadDate,@PSFundCreationDate, @ProposalNumber, @PSAccountNumber

WHILE @@FETCH_STATUS = 0
BEGIN

   SELECT @DaysAttachUploadToAccountCreation = NULL 

   if (@AttachmentUploadDate is not null and @PSFundCreationDate is not null)
	   execute infoedstaging.dbo.spBusinessDaysGet @AttachmentUploadDate, @PSFundCreationDate, @DaysAttachUploadToAccountCreation output
  
   update #tmpMetric_AwardActivations 
   set 	DaysAttachUploadToAccountCreation = @DaysAttachUploadToAccountCreation
   where ProposalNumber = @ProposalNumber and PSAccountNumber = @PSAccountNumber

   FETCH NEXT FROM BusinessDays_Cursor 
   INTO @AttachmentUploadDate,@PSFundCreationDate, @ProposalNumber, @PSAccountNumber

END

CLOSE BusinessDays_Cursor
DEALLOCATE BusinessDays_Cursor
--GO

INSERT INTO [InfoEdStaging].[dbo].[rptMetric_AwardActivations]
           (PeriodType,PeriodStartDate,[Institution]
           ,[ProposalNumber]
           ,[PIName]
           ,[ChiefCode]
           ,[Sponsor]
           ,[SponsorID]
           ,[ProposalStatus]
           ,[AttachmentType]
           ,[UploadDate]
           ,[UploadedBy]
           ,[StatusChanged]
           ,[LastUpdatedBy]
           ,[PSAccountNumber]
           ,[PSFundCreationDate]
           ,[DaysAttachUploadToAccountCreation]
           ,[PostAwardManager]
           ,[BudgetAmount]
           ,[InstrumentType])

SELECT PeriodType=@PeriodType,PeriodStartDate=@PeriodFrom,[Institution]
      ,[ProposalNumber]
      ,[PIName]
      ,[ChiefCode]
      ,[Sponsor]
      ,[SponsorID]=[SponosrID]
      ,[ProposalStatus]
      ,[AttachmentType]
      ,[UploadDate]
      ,[UploadedBy]
      ,[StatusChanged]
      ,[LastUpdatedBy]
      ,[PSAccountNumber]
      ,[PSFundCreationDate]
      ,[DaysAttachUploadToAccountCreation]
      ,[PostAwardManager]
      ,[BudgetAmount]
      ,[InstrumentType]
  FROM [#tmpMetric_AwardActivations]

/**End Of Award Activations **/

--284
--Awards to be Activated 
DELETE FROM [InfoEdStaging].[dbo].rptMetric_AwardsToBeActivated WHERE PeriodType=@PeriodType AND PeriodStartDate=@PeriodFrom

INSERT INTO [InfoEdStaging].[dbo].[rptMetric_AwardsToBeActivated]
           (PeriodType,PeriodStartDate,[Institution]
           ,[ProposalNumber]
           ,[PIName]
           ,[ChiefCode]
           ,[Sponsor]
           ,[SponsorID]
           ,[ProposalStatus]
           ,[UploadType]
           ,[UploadDate]
           ,[UploadedBy]
           ,[CoversheetUploadDate]
           ,[CoversheetUploadedBy]
           ,[PostAwardManager]
           ,[LastActivityComment]
           ,[LastActivityDate]
           ,[LastActivityEnterer])
select distinct PeriodType=@PeriodType,PeriodStartDate=@PeriodFrom,case i.int_unit_code when '1200' then 'MGH' when '2200' then 'BWH' when '1700' then 'IHP' when '1400' then 'SRH' 
 when '1600' then 'MCL' when '0100' then 'PHS' when '2810' then 'FH' when '4100' then 'NWH' else isNull(i.int_unit_code,'') end as Institution, m.projnumber as ProposalNumber, 
 f.lname + ', ' + f.fname as PIName, u.int_unit_code as ChiefCode, sp.spon_name as Sponsor, 
 isNull(isNull(s.spon_awd,s.spon_id),'') as SponsorID,  st.code_desc as ProposalStatus, max(c.code_desc) UploadType,
 max(v.UploadDate) as UploadDate, max(pf.lname + ', ' + pf.fname) as UploadedBy, max(cov.UploadDate) as CoversheetUploadDate, max(covf.lname + ', ' + covf.fname) as CoversheetUploadedBy,
 fpa.lname + ', ' + fpa.fname as PostAwardManager,
 convert(varchar(3000),a.comments) as LastActivityComment, a.UpdatedDate as LastActivityDate, fa.lname + ', ' + fa.fname as LastActivityEnterer
--into rptMetric_AwardsToBeActivated
from Projmain m
inner join proposal s on s.prop_no = m.prop_no and s.inst_code = m.inst_code
left outer join projmain par on par.prop_no = s.parentprop_no and par.inst_code = s.inst_code and par.system = 'pt'
inner join codetab st on st.codeid = s.prop_stat and st.arr_name = 'prop_stat'
inner join ProjDoc d on d.projid in (m.projid, par.projid)
	and d.doctype in (select code from codetab where arr_name = 'UP_LOAD' and code_desc in ('Notice of Award','Award Notice'))
inner join codetab c on c.code = d.doctype and c.arr_name = 'UP_LOAD'
inner join codetab pt on pt.codeid = s.prop_type and pt.arr_name = 'prop_type' and pt.code_desc in ('Master') 
left outer join projdocver v on v.docid = d.docid and v.uploaddate is not null
	and v.UploadDate = (select max(vv.UploadDate) from ProjDoc dd
			left outer join projdocver vv on vv.docid = dd.docid and vv.uploaddate is not null
			where dd.projid in (m.projid, par.projid))
left outer join faculty pf on pf.unique_id = v.UploadOwner
left outer join ProjDoc covd on covd.projid in (m.projid, par.projid) 
	and covd.doctype in (select code from codetab where arr_name = 'UP_LOAD' and code_desc in ('Partners Coversheet'))
left outer loop join codetab covc on covc.code = covd.doctype and covc.arr_name = 'UP_LOAD'
left outer join projdocver cov on cov.docid = covd.docid and cov.uploaddate is not null
	and cov.UploadDate = (select max(cvv.UploadDate) from ProjDoc cdd 
				inner join projdocver cvv on cvv.docid = cdd.docid 
				where cdd.doctype = covc.code and cvv.uploaddate is not null and cdd.projid in (m.projid, par.projid)
				and covd.docid = cdd.docid)
left outer join faculty covf on covf.unique_id = cov.UploadOwner
left outer join sponspas sp on sp.spon_code = s.spon_code and sp.inst_code = s.inst_code
left outer join proppds o on o.prop_no = m.prop_no and o.inst_code = m.inst_code and o.first_pd = 1
left outer join faculty f on o.unique_id = f.unique_id
left outer join unit u on u.unit_code = s.unit_code and u.inst_code = 'partners'
left outer join unit i on len(i.deptID) = 4 and i.deptId = left(u.deptId,4) and i.int_unit_code is not null
left outer join prop_u pa on pa.prop_no = s.prop_no and pa.inst_code = s.inst_code
left outer join faculty fpa on pa.p_chr_2 = fpa.unique_id
left outer join projmainactivity a on m.projid = a.projid and a.UpdatedDate = (select max(UpdatedDate) from projmainactivity where a.projid = projid)
left outer join faculty fa on fa.unique_id = a.unique_id
where (c.code_desc in ('Notice of Award','Award Notice') and st.code_desc like 'Pending%') 
and st.code_desc not like 'Fully Executed/Awarded - Activated'
and (pt.code_desc = 'Master' and len(m.projnumber) = 11)
group by case i.int_unit_code when '1200' then 'MGH' when '2200' then 'BWH' when '1700' then 'IHP' when '1400' then 'SRH' 
 when '1600' then 'MCL' when '0100' then 'PHS' when '2810' then 'FH' when '4100' then 'NWH' else isNull(i.int_unit_code,'') end, m.projnumber,  f.lname + ', ' + f.fname, u.int_unit_code, sp.spon_name,  
isNull(isNull(s.spon_awd,s.spon_id),''),  st.code_desc, fpa.lname + ', ' + fpa.fname, convert(varchar(3000),a.comments), a.UpdatedDate, fa.lname + ', ' + fa.fname
order by UploadDate desc



--PCRO Awards to be Activated 
DELETE FROM [InfoEdStaging].[dbo].rptMetric_PCROAwardsToBeActivated WHERE PeriodType=@PeriodType AND PeriodStartDate=@PeriodFrom

INSERT INTO [InfoEdStaging].[dbo].[rptMetric_PCROAwardsToBeActivated]
           (PeriodType,PeriodStartDate,[Institution]
           ,[ProposalNumber]
           ,[PIName]
           ,[ChiefCode]
           ,[Sponsor]
           ,[SponsorID]
           ,[ProposalStatus]
           ,[ExecutedDate]
           ,[UploadType]
           ,[UploadDate]
           ,[UploadedBy]
           ,[CoversheetUploadDate]
           ,[CoversheetUploadedBy]
           ,[PostAwardManager]
           ,[LastActivityComment]
           ,[LastActivityDate]
           ,[LastActivityEnterer])
select distinct PeriodType=@PeriodType,PeriodStartDate=@PeriodFrom,
case i.int_unit_code 
when '1200' then 'MGH' 
when '2200' then 'BWH' 
when '1700' then 'IHP' 
when '1400' then 'SRH' 
when '1600' then 'MCL' 
when '0100' then 'PHS' 
when '2810' then 'FH' 
when '4100' then 'NWH' 
else isNull(i.int_unit_code,'') 
end as Institution, 
m.projnumber as ProposalNumber, 
f.lname + ', ' + f.fname as PIName, 
u.int_unit_code as ChiefCode, 
sp.spon_name as Sponsor, 
 isNull(isNull(s.spon_awd,s.spon_id),'') as SponsorID,  
st.code_desc as ProposalStatus, 
max(ex.stat_date) as ExecutedDate, 
max(c.code_desc) UploadType,
max(v.UploadDate) as UploadDate, 
max(pf.lname + ', ' + pf.fname) as UploadedBy, 
max(cov.UploadDate) as CoversheetUploadDate, 
max(covf.lname + ', ' + covf.fname) as CoversheetUploadedBy,
 fpa.lname + ', ' + fpa.fname as PostAwardManager,
convert(varchar(3000),a.comments) as LastActivityComment, 
a.UpdatedDate as LastActivityDate, 
fa.lname + ', ' + fa.fname as LastActivityEnterer
--into rptMetric_PCROAwardsToBeActivated
from Projmain m
inner join proposal s 
	on s.prop_no = m.prop_no 
	and s.inst_code = m.inst_code
left outer join projmain par 
	on par.prop_no = s.parentprop_no 
	and par.inst_code = s.inst_code 
	and par.system = 'pt'
inner join codetab st 
	on st.codeid = s.prop_stat 
	and st.arr_name = 'prop_stat'
inner join ProjDoc d 
	on d.projid in (m.projid, par.projid)
	and d.doctype in (select code 
									from codetab 
									where arr_name = 'UP_LOAD' 
									and code_desc in ('CSCR - Contract','CSCR - Amendment'))
inner join codetab c 
	on c.code = d.doctype 
	and c.arr_name = 'UP_LOAD'
inner join codetab pt 
	on pt.codeid = s.prop_type 
	and pt.arr_name = 'prop_type' 
	and pt.code_desc in ('Master') 
left outer join projdocver v 
	on v.docid = d.docid 
and v.uploaddate is not null
	and v.UploadDate = (select max(vv.UploadDate) 
										from ProjDoc dd
										left outer join projdocver vv 
										on vv.docid = dd.docid 
										and vv.uploaddate is not null
										where dd.projid in (m.projid, par.projid))
left outer join faculty pf 
	on pf.unique_id = v.UploadOwner
left outer join ProjDoc covd 
	on covd.projid in (m.projid, par.projid) 
	and covd.doctype in (select code 
										from codetab where arr_name = 'UP_LOAD' 
										and code_desc in ('Partners Coversheet'))
left outer join codetab covc 
	on covc.code = covd.doctype 
	and covc.arr_name = 'UP_LOAD'
left outer join projdocver cov 
	on cov.docid = covd.docid 
	and cov.uploaddate is not null
	and cov.UploadDate = (select max(cvv.UploadDate) 
											from ProjDoc cdd 
											inner join projdocver cvv 
												on cvv.docid = cdd.docid 
											where cdd.doctype = covc.code 
											and cvv.uploaddate is not null
											and cdd.projid in (m.projid, par.projid)
											and covd.docid = cdd.docid)
left outer join faculty covf 
	on covf.unique_id = cov.UploadOwner
left outer join sponspas sp 
	on sp.spon_code = s.spon_code 
	and sp.inst_code = s.inst_code
left outer join proppds o 
	on o.prop_no = m.prop_no 
	and o.inst_code = m.inst_code 
	and o.first_pd = 1
left outer join faculty f 
	on o.unique_id = f.unique_id
left outer join unit u 
	on u.unit_code = s.unit_code and u.inst_code = 'partners'
left outer join unit i 
	on len(i.deptID) = 4 
	and i.deptId = left(u.deptId,4) 
	and i.int_unit_code is not null
left outer join prop_u pa 
	on pa.prop_no = s.prop_no 
	and pa.inst_code = s.inst_code
left outer join faculty fpa 
	on pa.p_chr_2 = fpa.unique_id
left outer join projmainactivity a 
	on m.projid = a.projid 
	and a.UpdatedDate = (select max(UpdatedDate) 
									from projmainactivity 
									where a.projid = projid)
left outer join faculty fa 
	on fa.unique_id = a.unique_id
left outer join prop_stat ex 
	on ex.prop_no = s.prop_no 
	and ex.inst_code = s.inst_code 
	and ex.id = (select max(id) 
						from prop_stat 
						where ex.prop_no = prop_no 
						and ex.inst_code = inst_code 
						and ex.prop_stat = prop_stat)
						and ex.prop_stat = (select codeid 
															from codetab 
															where arr_name = 'PROP_STAT' 
															and code_desc = 'CSCR - Executed')
INNER JOIN		prop_u pu--NoProjectBudget added by aab 9/5/2008
	ON		s.inst_code = pu.inst_code
	AND	s.prop_no = pu.prop_no
	AND	pu.p_log_94 = 0--NoProjectBudget is not checked
where ((c.code_desc in ('CSCR - Contract') 
	and st.code_desc = 'CSCR - Executed' 
	and covc.code_desc in ('Partners Coversheet')) 
		or		(c.code_desc in ('CSCR - Amendment') 
	and st.code_desc = 'CSCR - Executed' ))	
	and st.code_desc not like 'Fully Executed/Awarded - Activated'
	and (pt.code_desc = 'Master' 
	and len(m.projnumber) = 11) 
group by case i.int_unit_code when '1200' then 'MGH' when '2200' then 'BWH' when '1700' then 'IHP' when '1400' then 'SRH' 
 when '1600' then 'MCL' when '0100' then 'PHS' when '2810' then 'FH' when '4100' then 'NWH' else isNull(i.int_unit_code,'') end, m.projnumber,  f.lname + ', ' + f.fname, u.int_unit_code, sp.spon_name,  
isNull(isNull(s.spon_awd,s.spon_id),''),  st.code_desc, fpa.lname + ', ' + fpa.fname, convert(varchar(3000),a.comments), a.UpdatedDate, fa.lname + ', ' + fa.fname,pu.p_log_94
order by UploadDate desc

--Pre Award JIT Report
DELETE FROM [InfoEdStaging].[dbo].rptMetric_PreAwardJIT WHERE PeriodType=@PeriodType AND PeriodStartDate=@PeriodFrom

INSERT INTO [InfoEdStaging].[dbo].[rptMetric_PreAwardJIT]
           (PeriodType,PeriodStartDate,[Institution]
           ,[ProposalNumber]
           ,[PIName]
           ,[ChiefCode]
           ,[Sponsor]
           ,[SponsorID]
           ,[ProposalStatus]
           ,[UploadType]
           ,[UploadDate]
           ,[UploadedBy]
           ,[PreAwardManager]
           ,[PostAwardManager]
           ,[LastActivityComment]
           ,[LastActivityDate]
           ,[LastActivityEnterer])
select distinct PeriodType=@PeriodType,PeriodStartDate=@PeriodFrom, case i.int_unit_code when '1200' then 'MGH' when '2200' then 'BWH' when '1700' then 'IHP' when '1400' then 'SRH' 
 when '1600' then 'MCL' when '0100' then 'PHS' when '2810' then 'FH' when '4100' then 'NWH' else isNull(i.int_unit_code,'') end as Institution, m.projnumber as ProposalNumber, 
 f.lname + ', ' + f.fname as PIName, u.int_unit_code as ChiefCode, sp.spon_name as Sponsor, 
 isNull(isNull(s.spon_awd,s.spon_id),'') as SponsorID,  st.code_desc as ProposalStatus, c.code_desc as UploadType,
 v.UploadDate as UploadDate, pf.lname + ', ' + pf.fname as UploadedBy, fpr.lname + ', ' + fpr.fname as PreAwardManager,
 fpa.lname + ', ' + fpa.fname as PostAwardManager, convert(varchar(3000),a.comments) as LastActivityComment, a.UpdatedDate as LastActivityDate, fa.lname + ', ' + fa.fname as LastActivityEnterer
--into rptMetric_PreAwardJIT
from Projmain m
inner join proposal s on s.prop_no = m.prop_no and s.inst_code = m.inst_code
inner join codetab st on st.codeid = s.prop_stat and st.arr_name = 'prop_stat'
inner join ProjDoc d on m.projid = d.projid and d.doctype in (select code from codetab where arr_name = 'UP_LOAD' and code_desc = 'Just In Time')
inner join codetab pt on pt.codeid = s.prop_type and pt.arr_name = 'prop_type' and pt.code_desc = 'Master'
left outer join projdocver v on v.docid = d.docid and v.uploaddate is not null
and v.UploadDate = (select max(vv.UploadDate) from ProjDoc dd
			left outer join projdocver vv on vv.docid = dd.docid and vv.uploaddate is not null
			inner join codetab cc on cc.code = dd.doctype and cc.arr_name = 'UP_LOAD'
			where m.projid = dd.projid and cc.code_desc in ('Just In Time'))
left outer join faculty pf on pf.unique_id = v.UploadOwner
left outer join sponspas sp on sp.spon_code = s.spon_code
inner join codetab c on c.code = d.doctype and c.arr_name = 'UP_LOAD'
left outer join proppds o on o.prop_no = m.prop_no and o.inst_code = m.inst_code and o.first_pd = 1
left outer join faculty f on o.unique_id = f.unique_id
left outer join unit u on u.unit_code = s.unit_code and u.inst_code = 'partners'
left outer join unit i on len(i.deptID) = 4 and i.deptId = left(u.deptId,4) and i.int_unit_code is not null
left outer join prop_u pa on pa.prop_no = s.prop_no and pa.inst_code = s.inst_code
left outer join faculty fpa on pa.p_chr_2 = fpa.unique_id
left outer join faculty fpr on pa.p_chr_1 = fpr.unique_id
left outer join projmainactivity a on m.projid = a.projid 
	and a.UpdatedDate = (select max(UpdatedDate) from projmainactivity where a.projid = projid)
left outer join faculty fa on fa.unique_id = a.unique_id
where c.code_desc in ('Just In Time')
and st.code_desc not like 'Fully Executed/Awarded - Activated'
and st.code_desc like 'Pending%Sponsor'
and len(m.projnumber) = 11
order by UploadDate desc


--Pre Award JIT Report (Week Version)
DELETE FROM [InfoEdStaging].[dbo].rptMetric_PreAwardJITWeek WHERE PeriodType=@PeriodType AND PeriodStartDate=@PeriodFrom

INSERT INTO [InfoEdStaging].[dbo].[rptMetric_PreAwardJITWeek]
           (PeriodType,PeriodStartDate,[Institution]
           ,[ProposalNumber]
           ,[PIName]
           ,[ChiefCode]
           ,[Sponsor]
           ,[SponsorID]
           ,[ProposalStatus]
           ,[UploadType]
           ,[UploadDate]
           ,[UploadedBy]
           ,[PreAwardManager]
           ,[PostAwardManager]
           ,[LastActivityComment]
           ,[LastActivityDate]
           ,[LastActivityEnterer])
select distinct PeriodType=@PeriodType,PeriodStartDate=@PeriodFrom,case i.int_unit_code when '1200' then 'MGH' when '2200' then 'BWH' when '1700' then 'IHP' when '1400' then 'SRH' 
 when '1600' then 'MCL' when '0100' then 'PHS' when '2810' then 'FH' when '4100' then 'NWH' else isNull(i.int_unit_code,'') end as Institution, m.projnumber as ProposalNumber, 
 f.lname + ', ' + f.fname as PIName, u.int_unit_code as ChiefCode, sp.spon_name as Sponsor, 
 isNull(isNull(s.spon_awd,s.spon_id),'') as SponsorID,  st.code_desc as ProposalStatus, c.code_desc as UploadType,
 v.UploadDate as UploadDate, pf.lname + ', ' + pf.fname as UploadedBy, fpr.lname + ', ' + fpr.fname as PreAwardManager,
 fpa.lname + ', ' + fpa.fname as PostAwardManager, convert(varchar(3000),a.comments) as LastActivityComment, a.UpdatedDate as LastActivityDate, fa.lname + ', ' + fa.fname as LastActivityEnterer
--into rptMetric_PreAwardJITWeek
from Projmain m
inner join proposal s on s.prop_no = m.prop_no and s.inst_code = m.inst_code
inner join codetab st on st.codeid = s.prop_stat and st.arr_name = 'prop_stat'
inner join ProjDoc d on m.projid = d.projid and d.doctype in (select code from codetab where arr_name = 'UP_LOAD' and code_desc = 'Just In Time')
inner join codetab pt on pt.codeid = s.prop_type and pt.arr_name = 'prop_type' and pt.code_desc = 'Master'
left outer join projdocver v on v.docid = d.docid and v.uploaddate is not null
and v.UploadDate = (select max(vv.UploadDate) from ProjDoc dd
			left outer join projdocver vv on vv.docid = dd.docid and vv.uploaddate is not null
			inner join codetab cc on cc.code = dd.doctype and cc.arr_name = 'UP_LOAD'
			where m.projid = dd.projid and cc.code_desc in ('Just In Time'))
left outer join faculty pf on pf.unique_id = v.UploadOwner
left outer join sponspas sp on sp.spon_code = s.spon_code
inner join codetab c on c.code = d.doctype and c.arr_name = 'UP_LOAD'
left outer join proppds o on o.prop_no = m.prop_no and o.inst_code = m.inst_code and o.first_pd = 1
left outer join faculty f on o.unique_id = f.unique_id
left outer join unit u on u.unit_code = s.unit_code and u.inst_code = 'partners'
left outer join unit i on len(i.deptID) = 4 and i.deptId = left(u.deptId,4) and i.int_unit_code is not null
left outer join prop_u pa on pa.prop_no = s.prop_no and pa.inst_code = s.inst_code
left outer join faculty fpa on pa.p_chr_2 = fpa.unique_id
left outer join faculty fpr on pa.p_chr_1 = fpr.unique_id
left outer join projmainactivity a on m.projid = a.projid 
	and a.UpdatedDate = (select max(UpdatedDate) from projmainactivity where a.projid = projid)
left outer join faculty fa on fa.unique_id = a.unique_id
where c.code_desc in ('Just In Time')
and st.code_desc not like 'Fully Executed/Awarded - Activated'
and st.code_desc like 'Pending%Sponsor'
and len(m.projnumber) = 11 and UploadDate between @PeriodFrom and @PeriodTo
order by UploadDate desc

--Advance Account Report (based on Awards to be Activated Report)
DELETE FROM [InfoEdStaging].[dbo].rptMetric_AdvanceAccount WHERE PeriodType=@PeriodType AND PeriodStartDate=@PeriodFrom

INSERT INTO [InfoEdStaging].[dbo].[rptMetric_AdvanceAccount]
           (PeriodType,PeriodStartDate,[Institution]
           ,[ProposalNumber]
           ,[PIName]
           ,[ChiefCode]
           ,[Sponsor]
           ,[SponsorID]
           ,[ProposalStatus]
           ,[AccountStatus]
           ,[UploadType]
           ,[UploadDate]
           ,[UploadedBy]
           ,[PostAwardManager]
           ,[LastActivityComment]
           ,[LastActivityDate]
           ,[LastActivityEnterer]
           ,[PSAccountNumber]
           ,[ProjectStartDate])
select distinct PeriodType=@PeriodType,PeriodStartDate=@PeriodFrom,case i.int_unit_code when '1200' then 'MGH' when '2200' then 'BWH' when '1700' then 'IHP' when '1400' then 'SRH' 
 when '1600' then 'MCL' when '0100' then 'PHS' when '2810' then 'FH' when '4100' then 'NWH' else isNull(i.int_unit_code,'') end as Institution, m.projnumber as ProposalNumber, 
 f.lname + ', ' + f.fname as PIName, u.int_unit_code as ChiefCode, sp.spon_name as Sponsor, 
 isNull(isNull(s.spon_awd,s.spon_id),'') as SponsorID,  st.code_desc as ProposalStatus, acs.code_desc as AccountStatus, c.code_desc UploadType,
 v.UploadDate as UploadDate, pf.lname + ', ' + pf.fname as UploadedBy, fpa.lname + ', ' + fpa.fname as PostAwardManager,
 convert(varchar(3000),a.comments) as LastActivityComment, a.UpdatedDate as LastActivityDate, fa.lname + ', ' + fa.fname as LastActivityEnterer, 
 '#' + ac.instaccountno as PSAccountNumber, s.app_st_dt as ProjectStartDate
--into rptMetric_AdvanceAccount
from Projmain m
inner join proposal s on s.prop_no = m.prop_no and s.inst_code = m.inst_code
inner join codetab st on st.codeid = s.prop_stat and st.arr_name = 'prop_stat'
inner join ProjDoc d on m.projid = d.projid and d.doctype in (select code from codetab where arr_name = 'UP_LOAD' and code_desc in ('Notice of Award','Award Notice'))
inner join codetab pt on pt.codeid = s.prop_type and pt.arr_name = 'prop_type' and pt.code_desc = 'Master'
left outer join projdocver v on v.docid = d.docid and v.uploaddate is not null
and v.UploadDate = (select max(vv.UploadDate) from ProjDoc dd
			left outer join projdocver vv on vv.docid = dd.docid and vv.uploaddate is not null
			inner join codetab cc on cc.code = dd.doctype and cc.arr_name = 'UP_LOAD'
			where m.projid = dd.projid and cc.code_desc in ('Notice of Award','Award Notice'))
left outer join faculty pf on pf.unique_id = v.UploadOwner
left outer join sponspas sp on sp.spon_code = s.spon_code
inner join codetab c on c.code = d.doctype and c.arr_name = 'UP_LOAD' and c.code_desc in ('Notice of Award','Award Notice')
left outer join proppds o on o.prop_no = m.prop_no and o.inst_code = m.inst_code and o.first_pd = 1
left outer join faculty f on o.unique_id = f.unique_id
left outer join unit u on u.unit_code = s.unit_code and u.inst_code = 'partners'
left outer join unit i on len(i.deptID) = 4 and i.deptId = left(u.deptId,4) and i.int_unit_code is not null
left outer join prop_u pa on pa.prop_no = s.prop_no and pa.inst_code = s.inst_code
left outer join faculty fpa on pa.p_chr_2 = fpa.unique_id
left outer join projmainactivity a on m.projid = a.projid and a.UpdatedDate = (select max(UpdatedDate) from projmainactivity where a.projid = projid)
left outer join faculty fa on fa.unique_id = a.unique_id
inner join pt_acct act on m.prop_no = act.prop_no and m.inst_code = act.inst_code
inner join pmaccount ac on ac.instaccountno = act.hacct_no
inner join codetab acs on acs.arr_name = 'account_st' and acs.codeid = ac.account_st
where acs.code_desc = 'Advance'
and len(m.projnumber) = 11
order by UploadDate desc


--Advance Account Report (based on Awards to be Activated Report) (Week Version)
DELETE FROM [InfoEdStaging].[dbo].rptMetric_AdvanceAccountWeek WHERE PeriodType=@PeriodType AND PeriodStartDate=@PeriodFrom

INSERT INTO [InfoEdStaging].[dbo].[rptMetric_AdvanceAccountWeek]
           (PeriodType,PeriodStartDate,[Institution]
           ,[ProposalNumber]
           ,[PIName]
           ,[ChiefCode]
           ,[Sponsor]
           ,[SponsorID]
           ,[ProposalStatus]
           ,[AccountStatus]
           ,[UploadType]
           ,[UploadDate]
           ,[UploadedBy]
           ,[PostAwardManager]
           ,[LastActivityComment]
           ,[LastActivityDate]
           ,[LastActivityEnterer]
           ,[PSAccountNumber]
           ,[ProjectStartDate])
select distinct PeriodType=@PeriodType,PeriodStartDate=@PeriodFrom,case i.int_unit_code when '1200' then 'MGH' when '2200' then 'BWH' when '1700' then 'IHP' when '1400' then 'SRH' 
 when '1600' then 'MCL' when '0100' then 'PHS' when '2810' then 'FH' when '4100' then 'NWH' else isNull(i.int_unit_code,'') end as Institution, m.projnumber as ProposalNumber, 
 f.lname + ', ' + f.fname as PIName, u.int_unit_code as ChiefCode, sp.spon_name as Sponsor, 
 isNull(isNull(s.spon_awd,s.spon_id),'') as SponsorID,  st.code_desc as ProposalStatus, acs.code_desc as AccountStatus, c.code_desc UploadType,
 v.UploadDate as UploadDate, pf.lname + ', ' + pf.fname as UploadedBy, fpa.lname + ', ' + fpa.fname as PostAwardManager,
 convert(varchar(3000),a.comments) as LastActivityComment, a.UpdatedDate as LastActivityDate, fa.lname + ', ' + fa.fname as LastActivityEnterer, 
 '#' + ac.instaccountno as PSAccountNumber, s.app_st_dt as ProjectStartDate
--into rptMetric_AdvanceAccountWeek
from Projmain m
inner join proposal s on s.prop_no = m.prop_no and s.inst_code = m.inst_code
inner join codetab st on st.codeid = s.prop_stat and st.arr_name = 'prop_stat'
inner join ProjDoc d on m.projid = d.projid and d.doctype in (select code from codetab where arr_name = 'UP_LOAD' and code_desc in ('Notice of Award','Award Notice'))
inner join codetab pt on pt.codeid = s.prop_type and pt.arr_name = 'prop_type' and pt.code_desc = 'Master'
left outer join projdocver v on v.docid = d.docid and v.uploaddate is not null
and v.UploadDate = (select max(vv.UploadDate) from ProjDoc dd
			left outer join projdocver vv on vv.docid = dd.docid and vv.uploaddate is not null
			inner join codetab cc on cc.code = dd.doctype and cc.arr_name = 'UP_LOAD'
			where m.projid = dd.projid and cc.code_desc in ('Notice of Award','Award Notice'))
left outer join faculty pf on pf.unique_id = v.UploadOwner
left outer join sponspas sp on sp.spon_code = s.spon_code
inner join codetab c on c.code = d.doctype and c.arr_name = 'UP_LOAD' and c.code_desc in ('Notice of Award','Award Notice')
left outer join proppds o on o.prop_no = m.prop_no and o.inst_code = m.inst_code and o.first_pd = 1
left outer join faculty f on o.unique_id = f.unique_id
left outer join unit u on u.unit_code = s.unit_code and u.inst_code = 'partners'
left outer join unit i on len(i.deptID) = 4 and i.deptId = left(u.deptId,4) and i.int_unit_code is not null
left outer join prop_u pa on pa.prop_no = s.prop_no and pa.inst_code = s.inst_code
left outer join faculty fpa on pa.p_chr_2 = fpa.unique_id
left outer join projmainactivity a on m.projid = a.projid and a.UpdatedDate = (select max(UpdatedDate) from projmainactivity where a.projid = projid)
left outer join faculty fa on fa.unique_id = a.unique_id
inner join pt_acct act on m.prop_no = act.prop_no and m.inst_code = act.inst_code
inner join pmaccount ac on ac.instaccountno = act.hacct_no
inner join codetab acs on acs.arr_name = 'account_st' and acs.codeid = ac.account_st
where acs.code_desc = 'Advance'
and len(m.projnumber) = 11 and ac.createdate between @PeriodFrom and @PeriodTo -- Date Range
order by UploadDate desc

--Awarded but no Attachment 
DELETE FROM [InfoEdStaging].[dbo].rptMetric_AwardedButNoAttachment WHERE PeriodType=@PeriodType AND PeriodStartDate=@PeriodFrom

INSERT INTO [InfoEdStaging].[dbo].[rptMetric_AwardedButNoAttachment]
           (PeriodType,PeriodStartDate,[Institution]
           ,[ProposalNumber]
           ,[PIName]
           ,[ChiefCode]
           ,[Sponsor]
           ,[SponsorID]
           ,[ProposalStatus]
           ,[StatusUpdated]
           ,[LastUpdatedBy]
           ,[PostAwardManager]
           ,[PSAccountNumber])
select distinct PeriodType=@PeriodType,PeriodStartDate=@PeriodFrom,case i.int_unit_code when '1200' then 'MGH' when '2200' then 'BWH' when '1700' then 'IHP' when '1400' then 'SRH' 
 when '1600' then 'MCL' when '0100' then 'PHS' when '2810' then 'FH' else isNull(i.int_unit_code,'') end as Institution, m.projnumber as ProposalNumber, f.lname + ', ' + f.fname as PIName, 
 u.int_unit_code as ChiefCode, sp.spon_name as Sponsor, isNull(isNull(s.spon_awd,s.spon_id),'') as SponsorID,
st.code_desc as ProposalStatus, ps.stat_date as StatusUpdated, pst.lname + ', ' + pst.fname as LastUpdatedBy, 
fpa.lname + ', ' + fpa.fname as PostAwardManager, '#' + b.hacct as PSAccountNumber
--into rptMetric_AwardedButNoAttachment
from Projmain m
inner join proposal s on s.prop_no = m.prop_no and s.inst_code = m.inst_code and len(m.projnumber) = 11
left outer join projmain par on par.prop_no = s.parentprop_no and par.inst_code = s.inst_code and par.system = 'pt'
inner join codetab st on st.codeid = s.prop_stat and st.arr_name = 'prop_stat'
inner join codetab pt on pt.codeid = s.prop_type and pt.arr_name = 'prop_type' and pt.code_desc in ('Master')
left outer join proppds o on o.prop_no = m.prop_no and o.inst_code = m.inst_code and o.first_pd = 1
left outer join faculty f on o.unique_id = f.unique_id
left outer join unit u on u.unit_code = s.unit_code and u.inst_code = 'partners'
left outer join unit i on len(i.deptID) = 4 and i.deptId = left(u.deptId,4) and i.int_unit_code is not null
inner join prop_stat ps on ps.prop_no = s.prop_no and ps.inst_code = s.inst_code and ps.prop_stat = s.prop_stat
and ps.stat_date  = (select min(stat_date) from prop_stat where prop_no = s.prop_no and inst_code = s.inst_code and prop_stat = s.prop_stat)
left outer join sponspas sp on sp.spon_code = s.spon_code
left outer join faculty pst on ps.unique_id = pst.unique_id
left outer join prop_u pa on pa.prop_no = s.prop_no and pa.inst_code = s.inst_code
left outer join faculty fpa on pa.p_chr_2 = fpa.unique_id
left outer join pd_grc_bgt b on b.prop_no = s.prop_no and b.inst_code = s.inst_code and b.rec_type = '3' and b.n_bgtyr = 1
where pt.code_desc = 'Master' and len(m.projnumber) = 11
and ps.stat_date > '08/06/2007'
and ps.stat_date between @PeriodFrom and @PeriodTo  -- Date Range
and ((st.code_desc like 'Fully Executed/Awarded - Activated'
	and not exists (select * from ProjDoc d 
   				left outer join projdocver v on v.docid = d.docid and v.uploaddate is not null
   				inner join codetab c on c.code = d.doctype and c.arr_name = 'UP_LOAD'
   				and c.code_desc in ('Notice of Award','Award Notice', 'Subcontract Agreement In', 'Advance Fund Setup Form', 'Agreement')
  				 where d.projid in (m.projid, par.projid)))
)
order by ps.stat_date desc


--PCRO Awarded but no Attachment 
DELETE FROM [InfoEdStaging].[dbo].rptMetric_PCROAwardedButNoAttachment WHERE PeriodType=@PeriodType AND PeriodStartDate=@PeriodFrom

INSERT INTO [InfoEdStaging].[dbo].[rptMetric_PCROAwardedButNoAttachment]
           (PeriodType,PeriodStartDate,[Institution]
           ,[ProposalNumber]
           ,[PIName]
           ,[ChiefCode]
           ,[Sponsor]
           ,[SponsorID]
           ,[ProposalStatus]
           ,[StatusUpdated]
           ,[LastUpdatedBy]
           ,[PostAwardManager]
           ,[PSAccountNumber])
select distinct PeriodType=@PeriodType,PeriodStartDate=@PeriodFrom,case i.int_unit_code when '1200' then 'MGH' when '2200' then 'BWH' when '1700' then 'IHP' when '1400' then 'SRH' 
 when '1600' then 'MCL' when '0100' then 'PHS' when '2810' then 'FH' when '4100' then 'NWH' else isNull(i.int_unit_code,'') end as Institution, m.projnumber as ProposalNumber, f.lname + ', ' + f.fname as PIName, 
 u.int_unit_code as ChiefCode, sp.spon_name as Sponsor, isNull(isNull(s.spon_awd,s.spon_id),'') as SponsorID,
st.code_desc as ProposalStatus, ps.stat_date as StatusUpdated, pst.lname + ', ' + pst.fname as LastUpdatedBy, 
fpa.lname + ', ' + fpa.fname as PostAwardManager, '#' + b.hacct as PSAccountNumber
--into rptMetric_PCROAwardedButNoAttachment
from Projmain m
inner join proposal s on s.prop_no = m.prop_no and s.inst_code = m.inst_code and m.system = 'pt'
inner join codetab st on st.codeid = s.prop_stat and st.arr_name = 'prop_stat'
inner join codetab pt on pt.codeid = s.prop_type and pt.arr_name = 'prop_type' and pt.code_desc in ('Master')
left outer join projmain par on par.prop_no = s.parentprop_no and par.inst_code = s.inst_code and par.system = 'pt'
 and len(par.projnumber) = 11
left outer join proppds o on o.prop_no = m.prop_no and o.inst_code = m.inst_code and o.first_pd = 1
left outer join faculty f on o.unique_id = f.unique_id
left outer join unit u on u.unit_code = s.unit_code and u.inst_code = 'partners'
left outer join unit i on len(i.deptID) = 4 and i.deptId = left(u.deptId,4) and i.int_unit_code is not null
inner join prop_stat ps on ps.prop_no = s.prop_no and ps.inst_code = s.inst_code and ps.prop_stat = s.prop_stat
and ps.stat_date  = (select min(stat_date) from prop_stat where prop_no = s.prop_no and inst_code = s.inst_code and prop_stat = s.prop_stat)
left outer join sponspas sp on sp.spon_code = s.spon_code
left outer join faculty pst on ps.unique_id = pst.unique_id
left outer join prop_u pa on pa.prop_no = s.prop_no and pa.inst_code = s.inst_code
left outer join faculty fpa on pa.p_chr_2 = fpa.unique_id
left outer join pd_grc_bgt b on b.prop_no = s.prop_no and b.inst_code = s.inst_code and b.rec_type = '3' and b.n_bgtyr = 1
where pt.code_desc = 'Master' and len(m.projnumber) = 11
and ps.stat_date > '08/06/2007'
and ps.stat_date between @PeriodFrom and @PeriodTo  -- Date Range
and (st.code_desc like 'CSCR - Executed'
and (not exists (select * from ProjDoc d 
   				left outer join projdocver v on v.docid = d.docid and v.uploaddate is not null
   				inner join codetab c on c.code = d.doctype and c.arr_name = 'UP_LOAD'
   				and c.code_desc in ('CSCR - Contract')
  				 where d.projid in (m.projid, par.projid))
		or not exists (select * from ProjDoc d 
   				left outer join projdocver v on v.docid = d.docid and v.uploaddate is not null
   				inner join codetab c on c.code = d.doctype and c.arr_name = 'UP_LOAD'
   				and c.code_desc in ('Partners Coversheet')
  				 where d.projid in (m.projid, par.projid)))
   or (st.code_desc like 'CSCR - Executed'
	and not exists (select * from ProjDoc d 
   				left outer join projdocver v on v.docid = d.docid and v.uploaddate is not null
   				inner join codetab c on c.code = d.doctype and c.arr_name = 'UP_LOAD'
   				and c.code_desc in ('CSCR - Amendment')
  				 where d.projid in (m.projid, par.projid)))
)
order by ps.stat_date desc

--Awarded but not Funded
DELETE FROM [InfoEdStaging].[dbo].rptMetric_AwardedButNotFunded WHERE PeriodType=@PeriodType AND PeriodStartDate=@PeriodFrom

INSERT INTO [InfoEdStaging].[dbo].[rptMetric_AwardedButNotFunded]
           (PeriodType,PeriodStartDate,[Institution]
           ,[ProposalNumber]
           ,[PIName]
           ,[ChiefCode]
           ,[Sponsor]
           ,[SponsorID]
           ,[ProposalStatus]
           ,[StatusChangeDate]
           ,[LastUpdatedBy]
           ,[AwardChangeDate]
           ,[AwardedBy])
select distinct PeriodType=@PeriodType,PeriodStartDate=@PeriodFrom,case i.int_unit_code when '1200' then 'MGH' when '2200' then 'BWH' when '1700' then 'IHP' when '1400' then 'SRH' 
 when '1600' then 'MCL' when '0100' then 'PHS' when '2810' then 'FH' when '4100' then 'NWH' else isNull(i.int_unit_code,'') end as Institution, p.inst_no as ProposalNumber, 
 f.lname + ', ' + f.fname as PIName, u.int_unit_code as ChiefCode, sp.spon_name as Sponsor, 
 spon_awd as SponsorID, cc.code_desc as ProposalStatus, st.stat_date as StatusChangeDate, fs.lname + ', ' + fs.fname as LastUpdatedBy, 
 a.stat_date as AwardChangeDate, ast.lname + ', ' + ast.fname as AwardedBy 
--into rptMetric_AwardedButNotFunded
from proposal p
inner join pd_grc_bgt s on p.prop_no = s.prop_no and p.inst_code = s.inst_code and s.rec_type = '3'
inner join codetab c on c.arr_name = 'awd_stat' and s.bud_stat = c.codeid and c.code_desc = 'Awarded'
inner join codetab pt on pt.codeid = p.prop_type and pt.arr_name = 'prop_type' and pt.code_desc = 'Master'
inner join awd_stat a on a.prop_no = s.prop_no and a.inst_code = s.inst_code and a.prop_stat = s.bud_stat
and a.id = (select max(id) from awd_stat where prop_no = a.prop_no and inst_code = a.inst_code and prop_stat = a.prop_stat)
left outer join faculty ast on ast.unique_id = a.unique_id
inner join codetab cc on cc.arr_name = 'prop_stat' and p.prop_stat = cc.codeID 
inner join prop_stat st on p.prop_no = st.prop_no and p.prop_stat = st.prop_stat
and st.id = (select max(id) from prop_stat where prop_no = st.prop_no and inst_code = st.inst_code and prop_stat = st.prop_stat)
left outer join faculty fs on st.unique_id = fs.unique_id
left outer join proppds o on o.prop_no = p.prop_no and o.inst_code = p.inst_code and o.first_pd = 1
left outer join faculty f on o.unique_id = f.unique_id
left outer join unit u on u.unit_code = p.unit_code and u.inst_code = 'partners'
left outer join unit i on len(i.deptID) = 4 and i.deptId = left(u.deptId,4) and i.int_unit_code is not null
left outer join sponspas sp on sp.spon_code = p.spon_code
where isNull(s.hacct,'') = '' 
and a.stat_date between @PeriodFrom and @PeriodTo -- Date Range
order by a.stat_date


--Renewals Processed
DELETE FROM [InfoEdStaging].[dbo].rptMetric_RenewalsProcessed WHERE PeriodType=@PeriodType AND PeriodStartDate=@PeriodFrom

INSERT INTO [InfoEdStaging].[dbo].[rptMetric_RenewalsProcessed]
           (PeriodType,PeriodStartDate,[Institution]
           ,[ProposalNumber]
           ,[PIName]
           ,[ChiefCode]
           ,[Sponsor]
           ,[SponsorID]
           ,[ProposalStatus]
           ,[AwardPeriod]
           ,[AwardedBy]
           ,[AwardChangeDate]
           ,[UploadDate]
           ,[AwardStatus]
           ,[PostAwardManager])
select distinct PeriodType=@PeriodType,PeriodStartDate=@PeriodFrom,case i.int_unit_code when '1200' then 'MGH' when '2200' then 'BWH' when '1700' then 'IHP' when '1400' then 'SRH' 
 when '1600' then 'MCL' when '0100' then 'PHS' when '2810' then 'FH' when '4100' then 'NWH' else isNull(i.int_unit_code,'') end as Institution, p.inst_no as ProposalNumber, 
 f.lname + ', ' + f.fname as PIName, u.int_unit_code as ChiefCode, sp.spon_name as Sponsor, 
 spon_awd as SponsorID, cc.code_desc as ProposalStatus, max(isNull(s.awd_yr, s.n_bgtyr)) as AwardPeriod,
 ast.lname + ', ' + ast.fname as AwardedBy, a.stat_date as AwardChangeDate, 
 max(v.UploadDate) as UploadDate, c.code_desc as AwardStatus, fpa.lname + ', ' + fpa.fname as PostAwardManager
--into rptMetric_RenewalsProcessed
from projmain m inner join proposal p on m.prop_no = p.prop_no and m.inst_code = p.inst_code
inner join pd_grc_bgt s on p.prop_no = s.prop_no and p.inst_code = s.inst_code and s.rec_type = '3' and s.n_bgtyr > 1
inner join codetab c on c.arr_name = 'awd_stat' and s.bud_stat = c.codeid and c.code_desc = 'Awarded'
inner join codetab pt on pt.codeid = p.prop_type and pt.arr_name = 'prop_type' and pt.code_desc = 'Master'
inner join awd_stat a on a.prop_no = s.prop_no and a.inst_code = s.inst_code and a.prop_stat = s.bud_stat
and a.id = (select max(id) from awd_stat where prop_no = a.prop_no and inst_code = a.inst_code and prop_stat = a.prop_stat)
left outer join faculty ast on ast.unique_id = a.unique_id
inner join codetab cc on cc.arr_name = 'prop_stat' and p.prop_stat = cc.codeID and cc.code_desc = 'Fully Executed/Awarded - Activated'
inner join prop_stat st on p.prop_no = st.prop_no and p.prop_stat = st.prop_stat
and st.id = (select max(id) from prop_stat where prop_no = st.prop_no and inst_code = st.inst_code and prop_stat = st.prop_stat)
left outer join faculty fs on st.unique_id = fs.unique_id
left outer join proppds o on o.prop_no = p.prop_no and o.inst_code = p.inst_code and o.first_pd = 1
left outer join faculty f on o.unique_id = f.unique_id
left outer join unit u on u.unit_code = p.unit_code and u.inst_code = 'partners'
left outer join unit i on len(i.deptID) = 4 and i.deptId = left(u.deptId,4) and i.int_unit_code is not null
left outer join sponspas sp on sp.spon_code = p.spon_code
left outer join ProjDoc d on d.projid = m.projid 
	and d.doctype in (select code from codetab where arr_name = 'UP_LOAD' and code_desc in ('Notice of Award', 'Award Notice', 'Subcontract Agreement In', 'Advance Fund Setup Form', 'Agreement'))
left outer join projdocver v on v.docid = d.docid and v.uploaddate is not null 
	and v.UploadDate = (select max(vv.UploadDate) from ProjDoc dd
			left outer join projdocver vv on vv.docid = dd.docid and vv.uploaddate is not null
			inner join codetab cc on cc.code = dd.doctype and cc.arr_name = 'UP_LOAD'
			where m.projid = dd.projid and cc.code_desc in ('Notice of Award', 'Award Notice', 'Subcontract Agreement In', 'Advance Fund Setup Form', 'Agreement'))
left outer join codetab vc on vc.code = d.doctype and vc.arr_name = 'UP_LOAD' and vc.code_desc in ('Notice of Award', 'Award Notice', 'Subcontract Agreement In', 'Advance Fund Setup Form', 'Agreement') 
left outer join prop_u pa on pa.prop_no = s.prop_no and pa.inst_code = s.inst_code
left outer join faculty fpa on pa.p_chr_2 = fpa.unique_id
where a.stat_date > '01/01/2007' 
and a.stat_date between @PeriodFrom and @PeriodTo -- Date Range
group by i.int_unit_code, p.inst_no, f.lname, f.fname, u.int_unit_code, sp.spon_name, p.spon_awd, cc.code_desc, fs.lname,
fs.fname, st.stat_date, a.stat_date, ast.lname, ast.fname, c.code_desc, fpa.lname + ', ' + fpa.fname
order by a.stat_date desc

--Renewals to be Processed
DELETE FROM [InfoEdStaging].[dbo].rptMetric_RenewalsToBeProcessed WHERE PeriodType=@PeriodType AND PeriodStartDate=@PeriodFrom

INSERT INTO [InfoEdStaging].[dbo].[rptMetric_RenewalsToBeProcessed]
           (PeriodType,PeriodStartDate,[Institution]
           ,[ProposalNumber]
           ,[PIName]
           ,[ChiefCode]
           ,[Sponsor]
           ,[SponsorID]
           ,[AwardPeriod]
           ,[StartDate]
           ,[AwardPeriodStatus]
           ,[PostAwardManager]
           ,[UploadDateWithin60Days]
           ,[PSAccountNumber]
           ,[ProjectStart]
           ,[ProjectEnd]
           ,[InstrumentType])
select distinct PeriodType=@PeriodType,PeriodStartDate=@PeriodFrom,case i.int_unit_code when '1200' then 'MGH' when '2200' then 'BWH' when '1700' then 'IHP' when '1400' then 'SRH' 
 when '1600' then 'MCL' when '0100' then 'PHS' when '2810' then 'FH' when '4100' then 'NWH' else isNull(i.int_unit_code,'') end as Institution, p.inst_no as ProposalNumber, 
 f.lname + ', ' + f.fname as PIName, u.int_unit_code as ChiefCode, sp.spon_name as Sponsor, 
 spon_awd as SponsorID, min(s.n_bgtyr) as AwardPeriod, min(s.ap_st_dt) as StartDate, 
 c.code_desc as AwardPeriodStatus, fpa.lname + ', ' + fpa.fname as PostAwardManager, 
 case when max(v.UploadDate) between dateadd(dd,-60,getdate()) and getdate() then max(v.UploadDate) else null end as UploadDateWithin60Days, 
 '#' + max(s.hacct) as PSAccountNumber, p.app_st_dt as ProjectStart, p.app_end_dt as ProjectEnd, pa.p_sin_17 as InstrumentType
--into rptMetric_RenewalsToBeProcessed
from projmain m inner join proposal p on p.prop_no = m.prop_no and p.inst_code = m.inst_code
inner join pd_grc_bgt s on p.prop_no = s.prop_no and p.inst_code = s.inst_code and s.rec_type = '3' and s.n_bgtyr > 1
inner join codetab c on c.arr_name = 'awd_stat' and s.bud_stat = c.codeid and c.code_desc <> 'Awarded'
inner join codetab pt on pt.codeid = p.prop_type and pt.arr_name = 'prop_type' and pt.code_desc = 'Master'
inner join awd_stat a on a.prop_no = s.prop_no and a.inst_code = s.inst_code and a.prop_stat = s.bud_stat
and a.id = (select max(id) from awd_stat where prop_no = a.prop_no and inst_code = a.inst_code and prop_stat = a.prop_stat)
inner join codetab cc on cc.arr_name = 'prop_stat' and p.prop_stat = cc.codeID and cc.code_desc = 'Fully Executed/Awarded - Activated'
inner join prop_stat st on p.prop_no = st.prop_no and p.prop_stat = st.prop_stat
and st.id = (select max(id) from prop_stat where prop_no = st.prop_no and inst_code = st.inst_code and prop_stat = st.prop_stat)
left outer join faculty fs on st.unique_id = fs.unique_id
left outer join proppds o on o.prop_no = p.prop_no and o.inst_code = p.inst_code and o.first_pd = 1
left outer join faculty f on o.unique_id = f.unique_id
left outer join unit u on u.unit_code = p.unit_code and u.inst_code = 'partners'
left outer join unit i on len(i.deptID) = 4 and i.deptId = left(u.deptId,4) and i.int_unit_code is not null
left outer join sponspas sp on sp.spon_code = p.spon_code
left outer join prop_u pa on pa.prop_no = s.prop_no and pa.inst_code = s.inst_code
left outer join faculty fpa on pa.p_chr_2 = fpa.unique_id
left outer join ProjDoc d on d.projid = m.projid and d.doctype in (select code from codetab where arr_name = 'UP_LOAD' and code_desc in ('Notice of Award', 'Award Notice', 'Subcontract Agreement In', 'Advance Fund Setup Form', 'Agreement'))
left outer join projdocver v on v.docid = d.docid and v.uploaddate is not null 
	and v.UploadDate = (select max(vv.UploadDate) from ProjDoc dd
			left outer join projdocver vv on vv.docid = dd.docid and vv.uploaddate is not null
			inner join codetab cc on cc.code = dd.doctype and cc.arr_name = 'UP_LOAD'
			where m.projid = dd.projid and cc.code_desc in ('Notice of Award', 'Award Notice', 'Subcontract Agreement In', 'Advance Fund Setup Form', 'Agreement'))
where  p.app_end_dt > dateadd(mm, -12, getdate()) 
and (p.app_end_dt < getdate() or getdate() between dateadd(ww,-2,s.ap_st_dt) and s.ap_end_dt) -- budget end date is within this range
group by i.int_unit_code, u.int_unit_code, p.inst_no, f.lname, f.fname, sp.spon_name, p.spon_awd,  
	c.code_desc, fpa.lname, fpa.fname, p.app_st_dt, p.app_end_dt, pa.p_sin_17
HAVING min(s.ap_st_dt) > '01/01/2007'--changed by aab on 8/18/2008 requested by m. Gomperts sc ticket# 2609636
order by min(s.ap_st_dt)

--Accounts Inactivated
DELETE FROM [InfoEdStaging].[dbo].rptMetric_AccountsInactivated WHERE PeriodType=@PeriodType AND PeriodStartDate=@PeriodFrom

INSERT INTO [InfoEdStaging].[dbo].[rptMetric_AccountsInactivated]
           (PeriodType,PeriodStartDate,[Institution]
           ,[ProposalNumber]
           ,[PIName]
           ,[ChiefCode]
           ,[Sponsor]
           ,[SponsorID]
           ,[ProposalStatus]
           ,[StatusDate]
           ,[ProjectNumber]
           ,[LastUpdatedBy]
           ,[PostAwardManager])
select distinct PeriodType=@PeriodType,PeriodStartDate=@PeriodFrom,case i.int_unit_code when '1200' then 'MGH' when '2200' then 'BWH' when '1700' then 'IHP' when '1400' then 'SRH' 
 when '1600' then 'MCL' when '0100' then 'PHS' when '2810' then 'FH' when '4100' then 'NWH' else isNull(i.int_unit_code,'') end as Institution, p.inst_no as ProposalNumber, 
 f.lname + ', ' + f.fname as PIName, u.int_unit_code as ChiefCode, sp.spon_name as Sponsor, 
 spon_awd as SponsorID, cc.code_desc as ProposalStatus,  
ams.createdate as InactivationDate, --updated on 9/12/2008 by aab
'#' + a.hacct_no as FundNumber, 
fs.lname + ', ' + fs.fname as InactivationBy,--updated on 9/12/2008 by aab  
fpa.lname + ', ' + fpa.fname as PostAwardManager
--into rptMetric_AccountsInactivated
from proposal p
inner join pt_acct a 
	on p.prop_no = a.prop_no 
	and p.inst_code = a.inst_code 
inner join projmain am 
	on am.projnumber = a.hacct_no 
	and am.system = 'pm' 
inner join projobject ao 
	on am.projid = ao.projid 
inner join pmaccount pm 
	on pm.instaccountno = a.hacct_no
inner join codetab ast 
	on ast.codeid = pm.account_st 
	and ast.arr_name = 'Account_St' 
	and ast.code_desc in ('Inactive','Reserved')
left outer join projobjectstatus ams
	on ams.objectid = ao.objectid 
	and pm.account_st = ams.appr_stat
	AND	ams.CreateDate = (select max(CreateDate) --updated on 9/30/2008 by aab
										from projobjectstatus 
										where objectid = ams.ObjectId 
										and appr_stat = ams.appr_stat)
inner join codetab pt 
	on pt.codeid = p.prop_type 
	and pt.arr_name = 'prop_type' 
	and pt.code_desc = 'Master'
left outer join codetab cc 
	on cc.arr_name = 'prop_stat' 
	and p.prop_stat = cc.codeID
left outer join codetab c --updated on 9/30/2008 by aab
	on c.codeid = ams.appr_stat
left outer join faculty fs --updated on 9/30/2008 by aab
	on fs.unique_id = ams.unique_id
left outer join proppds o 
	on o.prop_no = p.prop_no 
	and o.inst_code = p.inst_code 
	and o.first_pd = 1
left outer join faculty f 
	on o.unique_id = f.unique_id
left outer join unit u 
	on u.unit_code = p.unit_code 
	and u.inst_code = 'partners'
left outer join unit i 
	on len(i.deptID) = 4 
	and i.deptId = left(u.deptId,4) 
	and i.int_unit_code is not null
left outer join sponspas sp 
	on sp.spon_code = p.spon_code
left outer join prop_u pa 
	on pa.prop_no = p.prop_no 
	and pa.inst_code = p.inst_code
left outer join faculty fpa 
	on pa.p_chr_2 = fpa.unique_id
where not exists (select * 
							 from pt_acct aa 
							 inner join pmaccount pmm 
									on pmm.instaccountno = aa.hacct_no 
							 inner join codetab astt 
									on astt.codeid = pmm.account_st 
									and astt.arr_name = 'Acount_St' 
									and astt.code_desc = 'Active'
							 where aa.prop_no = p.prop_no 
								and aa.inst_code = p.prop_no)
 and isNull(ams.updateddate, ams.createdate) between @PeriodFrom and @PeriodTo -- Date Range
order by ams.createdate desc


--Projects Ending in 30 days 
DELETE FROM [InfoEdStaging].[dbo].rptMetric_ProjectsEnding30 WHERE PeriodType=@PeriodType AND PeriodStartDate=@PeriodFrom

INSERT INTO [InfoEdStaging].[dbo].[rptMetric_ProjectsEnding30]
           (PeriodType,PeriodStartDate,[Institution]
           ,[ProposalNumber]
           ,[PIName]
           ,[ChiefCode]
           ,[Sponsor]
           ,[SponsorID]
           ,[ProposalStatus]
           ,[ProjectEnd]
           ,[PostAwardManager])
select distinct PeriodType=@PeriodType,PeriodStartDate=@PeriodFrom,case i.int_unit_code when '1200' then 'MGH' when '2200' then 'BWH' when '1700' then 'IHP' when '1400' then 'SRH' 
 when '1600' then 'MCL' when '0100' then 'PHS' when '2810' then 'FH' when '4100' then 'NWH' else isNull(i.int_unit_code,'') end as Institution, p.inst_no as ProposalNumber, 
 f.lname + ', ' + f.fname as PIName, u.int_unit_code as ChiefCode, sp.spon_name as Sponsor, 
 spon_awd as SponsorID, cc.code_desc as ProposalStatus, p.app_end_dt as ProjectEnd, fpa.lname + ', ' + fpa.fname as PostAwardManager
--into rptMetric_ProjectsEnding30
from proposal p
inner join codetab pt on pt.codeid = p.prop_type and pt.arr_name = 'prop_type' and pt.code_desc = 'Master'
inner join codetab cc on cc.arr_name = 'prop_stat' and p.prop_stat = cc.codeID 
	and cc.code_desc not like 'Closed%' 
left outer join proppds o on o.prop_no = p.prop_no and o.inst_code = p.inst_code and o.first_pd = 1
left outer join faculty f on o.unique_id = f.unique_id
left outer join unit u on u.unit_code = p.unit_code and u.inst_code = 'partners'
left outer join unit i on len(i.deptID) = 4 and i.deptId = left(u.deptId,4) and i.int_unit_code is not null
left outer join sponspas sp on sp.spon_code = p.spon_code
left outer join prop_u pa on pa.prop_no = p.prop_no and pa.inst_code = p.inst_code
left outer join faculty fpa on pa.p_chr_2 = fpa.unique_id
where p.app_end_dt between getdate() and dateadd(dd,30,getdate()) --Period = 30 days
-- where p.app_end_dt between getdate() and dateadd(dd,60,getdate()) --Period = 60 days
 --where p.app_end_dt between getdate() and dateadd(dd,90,getdate()) --Period = 90 days
order by p.app_end_dt asc

--Projects Ending in 60 days 
DELETE FROM [InfoEdStaging].[dbo].rptMetric_ProjectsEnding60 WHERE PeriodType=@PeriodType AND PeriodStartDate=@PeriodFrom

INSERT INTO [InfoEdStaging].[dbo].[rptMetric_ProjectsEnding60]
           (PeriodType,PeriodStartDate,[Institution]
           ,[ProposalNumber]
           ,[PIName]
           ,[ChiefCode]
           ,[Sponsor]
           ,[SponsorID]
           ,[ProposalStatus]
           ,[ProjectEnd]
           ,[PostAwardManager])
select distinct PeriodType=@PeriodType,PeriodStartDate=@PeriodFrom,case i.int_unit_code when '1200' then 'MGH' when '2200' then 'BWH' when '1700' then 'IHP' when '1400' then 'SRH' 
 when '1600' then 'MCL' when '0100' then 'PHS' when '2810' then 'FH' when '4100' then 'NWH' else isNull(i.int_unit_code,'') end as Institution, p.inst_no as ProposalNumber, 
 f.lname + ', ' + f.fname as PIName, u.int_unit_code as ChiefCode, sp.spon_name as Sponsor, 
 spon_awd as SponsorID, cc.code_desc as ProposalStatus, p.app_end_dt as ProjectEnd, fpa.lname + ', ' + fpa.fname as PostAwardManager
--into rptMetric_ProjectsEnding60
from proposal p
inner join codetab pt on pt.codeid = p.prop_type and pt.arr_name = 'prop_type' and pt.code_desc = 'Master'
inner join codetab cc on cc.arr_name = 'prop_stat' and p.prop_stat = cc.codeID 
	and cc.code_desc not like 'Closed%' 
left outer join proppds o on o.prop_no = p.prop_no and o.inst_code = p.inst_code and o.first_pd = 1
left outer join faculty f on o.unique_id = f.unique_id
left outer join unit u on u.unit_code = p.unit_code and u.inst_code = 'partners'
left outer join unit i on len(i.deptID) = 4 and i.deptId = left(u.deptId,4) and i.int_unit_code is not null
left outer join sponspas sp on sp.spon_code = p.spon_code
left outer join prop_u pa on pa.prop_no = p.prop_no and pa.inst_code = p.inst_code
left outer join faculty fpa on pa.p_chr_2 = fpa.unique_id
-- where p.app_end_dt between getdate() and dateadd(dd,30,getdate()) --Period = 30 days
where p.app_end_dt between getdate() and dateadd(dd,60,getdate()) --Period = 60 days
 --where p.app_end_dt between getdate() and dateadd(dd,90,getdate()) --Period = 90 days
order by p.app_end_dt asc

--Projects Ending in 90 days 
DELETE FROM [InfoEdStaging].[dbo].rptMetric_ProjectsEnding90 WHERE PeriodType=@PeriodType AND PeriodStartDate=@PeriodFrom

INSERT INTO [InfoEdStaging].[dbo].[rptMetric_ProjectsEnding90]
           (PeriodType,PeriodStartDate,[Institution]
           ,[ProposalNumber]
           ,[PIName]
           ,[ChiefCode]
           ,[Sponsor]
           ,[SponsorID]
           ,[ProposalStatus]
           ,[ProjectEnd]
           ,[PostAwardManager])
select distinct PeriodType=@PeriodType,PeriodStartDate=@PeriodFrom,case i.int_unit_code when '1200' then 'MGH' when '2200' then 'BWH' when '1700' then 'IHP' when '1400' then 'SRH' 
 when '1600' then 'MCL' when '0100' then 'PHS' when '2810' then 'FH' when '4100' then 'NWH' else isNull(i.int_unit_code,'') end as Institution, p.inst_no as ProposalNumber, 
 f.lname + ', ' + f.fname as PIName, u.int_unit_code as ChiefCode, sp.spon_name as Sponsor, 
 spon_awd as SponsorID, cc.code_desc as ProposalStatus, p.app_end_dt as ProjectEnd, fpa.lname + ', ' + fpa.fname as PostAwardManager
--into rptMetric_ProjectsEnding90
from proposal p
inner join codetab pt on pt.codeid = p.prop_type and pt.arr_name = 'prop_type' and pt.code_desc = 'Master'
inner join codetab cc on cc.arr_name = 'prop_stat' and p.prop_stat = cc.codeID 
	and cc.code_desc not like 'Closed%' 
left outer join proppds o on o.prop_no = p.prop_no and o.inst_code = p.inst_code and o.first_pd = 1
left outer join faculty f on o.unique_id = f.unique_id
left outer join unit u on u.unit_code = p.unit_code and u.inst_code = 'partners'
left outer join unit i on len(i.deptID) = 4 and i.deptId = left(u.deptId,4) and i.int_unit_code is not null
left outer join sponspas sp on sp.spon_code = p.spon_code
left outer join prop_u pa on pa.prop_no = p.prop_no and pa.inst_code = p.inst_code
left outer join faculty fpa on pa.p_chr_2 = fpa.unique_id
-- where p.app_end_dt between getdate() and dateadd(dd,30,getdate()) --Period = 30 days
-- where p.app_end_dt between getdate() and dateadd(dd,60,getdate()) --Period = 60 days
 where p.app_end_dt between getdate() and dateadd(dd,90,getdate()) --Period = 90 days
order by p.app_end_dt asc






GO


